package com.wxq.draw;

import java.io.File;
import java.text.Collator;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.graphics.PointF;
import android.util.Log;

import com.wxq.utility.QUtility;


public class DrawDBTool {

	private final Context context;
	private SQLiteDatabase db;
	private final DrawSQLiteOpenHelper dbOpenHelper;
	private String path="phone3";
	public DrawDBTool(Context context) {
		super();
		this.context = context;
		dbOpenHelper = new DrawSQLiteOpenHelper(
				this.context, 
				DrawConstants.DATABASE_NAME, 
				null, 
				DrawConstants.DATABASE_VERSION);
	}
	public void close() {
		if(db!=null && db.isOpen())
		{
			db.close();
		}	
	}
	
	public void open() {
		try {
			//db = dbOpenHelper.getReadableDatabase();
			//db =this.openOrCreateDatabase("jiao3.db",MODE_PRIVATE, null);///////////! !! !!! !!!! !!!!! !!!!
			db =SQLiteDatabase.openOrCreateDatabase(android.os.Environment.getExternalStorageDirectory()+DrawConstants.DBFolder+path+".db", null);
		} catch (SQLiteException e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	public void setDBpath(String filename){
		if(db!=null && db.isOpen())
		{
			db.close();
		}
		File dbFile = new File(android.os.Environment.getExternalStorageDirectory()+DrawConstants.DBFolder+filename+".db");
		if(dbFile.exists())
		{
			path=filename;
		}
		else {
			Log.d("lxr", "database"+filename+".db not existed!");
		}
		open();
	}
	
	//-----------获得画图轮廓------------------------------------------
	public Cursor getDrawItems(String floorName) {
		String sql = DrawConstants.QUERY_DRAW_ITEMS + "'"+floorName+"'";
		if(db == null || !db.isOpen())
		{
			open();	
		}
		//Log.d("wxq", "sql="+sql);
		Cursor cursor = db.rawQuery(sql, null);
		return cursor;
	}
	
	//-----------获得渲染------------------------------------------
		public Cursor getRenderItems(String floorName) {
			String sql = DrawConstants.QUERY_RENDER_ITEMS ;
			if(db == null || !db.isOpen())
			{
				open();	
			}
			//Log.d("wxq", "sql="+sql);
			Cursor cursor = db.rawQuery(sql, null);
			return cursor;
		}
		
	//-----------获得商铺信息------------------------------------------
	public Map<String, String> getSpotInfo(String spotId) {
		Map<String, String> spotInfo = new HashMap<String, String>();
			String sql = DrawConstants.QUERY_SPOT_INFO +"'"+spotId+"'" ;
			if(db == null || !db.isOpen())
			{
				open();	
			}
			Cursor cursor = db.rawQuery(sql, null);
			if (cursor.moveToNext()) {
				spotInfo.put("SpotID", cursor.getString(cursor.getColumnIndex("SpotID")));
				spotInfo.put("CompanyName", cursor.getString(cursor.getColumnIndex("CompanyName")));
				spotInfo.put("Display", cursor.getString(cursor.getColumnIndex("Display")));
				spotInfo.put("MapName", cursor.getString(cursor.getColumnIndex("MapName")));
				spotInfo.put("Type", cursor.getString(cursor.getColumnIndex("Type")));
				spotInfo.put("Address", cursor.getString(cursor.getColumnIndex("Address")));
				spotInfo.put("Keywords", cursor.getString(cursor.getColumnIndex("Keywords")));
				spotInfo.put("Phone", cursor.getString(cursor.getColumnIndex("Phone")));
				spotInfo.put("Email", cursor.getString(cursor.getColumnIndex("Email")));
				spotInfo.put("Website", cursor.getString(cursor.getColumnIndex("Website")));
				spotInfo.put("Profile", cursor.getString(cursor.getColumnIndex("Profile")));
				spotInfo.put("OpeningTime", cursor.getString(cursor.getColumnIndex("OpeningTime")));
				spotInfo.put("Coord_X", cursor.getString(cursor.getColumnIndex("Coord_X")));
				spotInfo.put("Coord_Y", cursor.getString(cursor.getColumnIndex("Coord_Y")));
				spotInfo.put("image", cursor.getString(cursor.getColumnIndex("Image")));
				spotInfo.put("MoreInfo", cursor.getString(cursor.getColumnIndex("MoreInfo")));
				spotInfo.put("UnitTypeID", cursor.getString(cursor.getColumnIndex("UnitTypeID")));
				spotInfo.put("UnitID", cursor.getString(cursor.getColumnIndex("UnitID")));
				spotInfo.put("Removed", cursor.getString(cursor.getColumnIndex("Removed")));
			}
			cursor.close();
			return spotInfo;
	}
	
	//获得类别详细信息
	public List<Map<String, String>> getPOITypeFields(String POITypeId) {
		Log.d("wxq","Enter getPoiTyteFields,POITypeId="+POITypeId);
		List<Map<String, String>> list = new ArrayList<Map<String,String>>();
		
		String sql = DrawConstants.QUERY_POITYPE_FIELDS ;
		if(db == null || !db.isOpen())
		{
			open();	
		}
		Cursor cursor = db.rawQuery(sql, new String []{POITypeId});
		while(cursor.moveToNext()) {
			Map<String, String> map = new HashMap<String, String>();
			map.put("ID", cursor.getString(cursor.getColumnIndex("ID")));
			map.put("Field", cursor.getString(cursor.getColumnIndex("Field")));
			map.put("Name", cursor.getString(cursor.getColumnIndex("Name")));
			map.put("ComboMode", cursor.getString(cursor.getColumnIndex("ComboMode")));
			map.put("ListSource", cursor.getString(cursor.getColumnIndex("ListSource")));
			list.add(map);
		}
		cursor.close();
		
		Log.d("wxq", "list = "+list);
		return list;
	}
	
	//-----------获得楼层数组------------------------------------------
	public List<String> getFloorNames() {
		List<String> list = new ArrayList<String>();
		String sql = DrawConstants.QUERY_FLOORS;
		if(db == null || !db.isOpen())
		{
			open();	
		}
		Cursor cursor = db.rawQuery(sql, null);
		while (cursor.moveToNext()) {
			list.add(cursor.getString(cursor.getColumnIndex("MapName")));
		}
		cursor.close();
		return list;
	}
	
	//-----------获得楼层数组------------------------------------------
		public int updateSpotInfo(String spotId,ContentValues contentValues) {
			if(db == null || !db.isOpen())
			{
				open();	
			}
			return db.update("InfoTable", contentValues, "SpotID = ?", new String [] {spotId});
		}
		
	//--------往draw添加记录-----------------
	public long addSpotToDraw(ContentValues contentValues) {
			if(db == null || !db.isOpen())
			{
				open();	
			}
			return db.insert("Draw", null, contentValues);
	}
	
	//--------往InfoTable添加记录-----------------
	public long addSpotToInfoTable(ContentValues contentValues) {
		if(db == null || !db.isOpen())
		{
			open();	
		}
		return db.insert("InfoTable", null, contentValues);
	}
	
	//--------删除记录-----------------
	public int deleteSpot(String spotId) {
		if(db == null || !db.isOpen())
		{
			open();	
		}
		ContentValues contentValues = new ContentValues();
		contentValues.put("Removed", 2+"");//删除
		db.update("InfoTable", contentValues, "SpotID = ?", new String [] {spotId});
		return db.delete("Draw", "SpotId = ? ", new String[]{spotId});
	}
	
	//----------------更新moreInfo数据----------------
	public int updateMoreInfo(String spotId,String xmlContent) {
		if(db == null || !db.isOpen())
		{
			open();	
		}
		ContentValues contentValues = new ContentValues();
		contentValues.put("MoreInfo", xmlContent);
		return db.update("InfoTable", contentValues, "SpotID = ?", new String [] {spotId});
	}
	
	//----------看是否outdoor---------------
	public boolean isOutdoor() {
		if(db == null || !db.isOpen())
		{
			open();	
		}
		Cursor cursor = db.query("maptype", null, null, null, null, null, null);
		if (cursor == null) {
			return false;
		}
		if (cursor.moveToNext()) {
			String result = cursor.getString(cursor.getColumnIndex("outdoor"));
			cursor.close();
			if ("True".equals(result)) {
				return true;
			}
			else {
				return false;
			}
		}
		else {
			return false;
		}
	}
	
	//--------往outdoor添加记录-----------------
		public long changeCoord2Outdoor(String spotId,double lat,double lon) {
			if(db == null || !db.isOpen())
			{
				open();	
			}
			ContentValues contentValues = new ContentValues();
			contentValues.put("Latitude", lat+"");
			contentValues.put("Longitude", lon+"");
			return db.update("outdoor", contentValues, "SpotID = ?", new String[]{spotId});
		}
		
	//---------获得地点的经纬度-------------------
		public Map<String, String> getCoordFromOutdoor(String spotId)
		{
			if(db == null || !db.isOpen())
			{
				open();	
			}
			Cursor cursor = db.query("outdoor", null, "SpotID = ?", new String []{spotId}, null, null, null);
			Map<String, String> map = new HashMap<String, String>();
			if (cursor.moveToNext()) {
				map.put("Latitude", cursor.getString(cursor.getColumnIndex("Latitude")));
				map.put("Longitude", cursor.getString(cursor.getColumnIndex("Longitude")));
			}
			cursor.close();
			return map;
		}
		
	//------------添加地点经纬度----------------------
		public void addCoord2Outdoor(String spotId) {
			if(db == null || !db.isOpen())
			{
				open();	
			}
			ContentValues contentValues = new ContentValues();
			contentValues.put("SpotID", spotId);
			db.insert("outdoor", null, contentValues);
		}
		
	//--------------获取地图unitType-------------------
		public Map<String, String> getUnitTypeMap() {
			if(db == null || !db.isOpen())
			{
				open();	
			}
			Map<String, String> map = new HashMap<String, String>();
			
			Cursor cursor = db.query("POIType", null, null, null, null, null, null);
			while (cursor.moveToNext()) {
				String ID = cursor.getString(cursor.getColumnIndex("ID"));
				String poiTypeName = cursor.getString(cursor.getColumnIndex("POITypeName"));
				map.put(ID, poiTypeName);
			}
			cursor.close();
			return map;
		}
		
		public static List<Map<String, String>> getUnitTypeList(Map<String, String> map) {
			Set<String> keys = map.keySet();
			List<Map<String, String>> list = new ArrayList<Map<String,String>>();
			for (String key : keys) {
				Map<String, String> tempMap = new HashMap<String, String>();
				tempMap.put("ID", key);
				tempMap.put("POITypeName", map.get(key));
				
				list.add(tempMap);
			}
			
			Collections.sort(list, new Comparator<Map<String, String>>() {

				@Override
				public int compare(Map<String, String> lhs, Map<String, String> rhs) {
					// 比较器方法
					String name1 = lhs.get("POITypeName");
					String name2 = rhs.get("POITypeName");
					Collator cmp = Collator.getInstance(java.util.Locale.CHINA); 
					return cmp.compare(name1, name2);
				}
			});
			
			return list;
		}
		
		public static List<String> getUnitTypeNames(List<Map<String, String>> list) {
			List<String> names = new ArrayList<String>();
			for (int i = 0; i < list.size(); i++) {
				names.add(list.get(i).get("POITypeName"));
			}
			return names;
		}
		
		public void changeUnitType(String spotId,String unitTypeId) {
			if(db == null || !db.isOpen())
			{
				open();	
			}
			ContentValues contentValues = new ContentValues();
			contentValues.put("UnitTypeID", unitTypeId);
			db.update("InfoTable", contentValues, "SpotID = ?", new String[]{spotId});
		}
	
	//-----------装载数据库------------------------------------------
		public static void setupDatabase(Context context,String dbPath,String dbFileName) {
			DrawConstants.DATABASE_PATH = dbPath;
			DrawConstants.DATABASE_NAME = dbFileName;
			String fileFullName = DrawConstants.DATABASE_PATH+DrawConstants.DATABASE_NAME;
			File dbFile = new File(fileFullName);
			if(!dbFile.exists())
			{
				// 创建目标文件
			    QUtility.CreateFile(fileFullName);
				QUtility.copyFileFromAssets(context, DrawConstants.DATABASE_NAME, dbFile);
				Log.d("wxq", "setup database");
			}
			else {
				Log.d("wxq", "database already existed!");
			}
		}
		
		
	//-------------------lxr:在image表项添加一个jpg---------------------！！！！！！！！！！！！！！！！！！！！！！！！！	
		public void addimage(String ID, String str) {
			String sql="update InfoTable set Image=ifnull(image,'')||'"+str+"' where SpotID = '"+ ID+"'";
			db.execSQL(sql);
			 Log.i("database",sql+"loaded successfully!"); 
		}
	//-------------------lxr:在image表项删除一个jpg---------------------！！！！！！！！！！！！！！！！！！！！！！！！！	
		public void delectimage(String ID, int position) {
			Map<String, String> spotInfo = getSpotInfo(ID);
		    String imageinfo=spotInfo.get("image");
		    char imagechange[]=imageinfo.toCharArray();
		    int i,n,count=0;
		    Log.d("lxr","before:"+imageinfo );
		    Log.d("lxr",imagechange.toString()+"");
		    for(i=0,n=0;n<=position;i++,n++){ 
		       count=0;
		    	for(;imagechange[i]!=';';i++){count++;}
		    } 
		    count++;
		    i-=count;
		    Log.d("lxr","count:"+count+"          piont="+i+"          length="+imagechange.length);

		    String front=imageinfo.substring(0,i);
		    String back=imageinfo.substring(i+count,imageinfo.length());
		    Log.d("lxr","front:"+front );
		    Log.d("lxr","back:"+back );
		    imageinfo=front+back;
		    Log.d("lxr","after:"+imageinfo );
		   
		    String sql="update InfoTable set Image='"+imageinfo+"' where SpotID = '"+ ID+"'";
			db.execSQL(sql);
			Log.i("database",sql+"loaded successfully!"); 
		}
		//lxr-----------获得地点数组------------------------------------------
		public  List<Map<String, Object>> getSiteNames(String floor) {
			List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
			String sql = "SELECT * FROM InfoTable Where Mapname='"+floor+"'";   Log.d("lxr",sql );
			if(db == null || !db.isOpen()){open();}
			Cursor cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("CompanyName", cursor.getString(cursor.getColumnIndex("CompanyName")));
				map.put("Coord_X", cursor.getFloat(cursor.getColumnIndex("Coord_X")));
				map.put("Coord_Y", cursor.getFloat(cursor.getColumnIndex("Coord_Y")));
				list.add(map);
			}
			cursor.close();
			close();
			return list;
		}
		
		//lxr-----------获得地点数组------------------------------------------
		public  List<Map<String, Object>> SearchSiteNames(String floor,String keyword) {
			List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
			String sql = "SELECT * FROM InfoTable Where Mapname='"+floor+"' AND CompanyName LIKE '%"+ keyword +"%'";   Log.d("lxr",sql );
			if(db == null || !db.isOpen()){open();}
			Cursor cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("CompanyName", cursor.getString(cursor.getColumnIndex("CompanyName")));
				map.put("Coord_X", cursor.getFloat(cursor.getColumnIndex("Coord_X")));
				map.put("Coord_Y", cursor.getFloat(cursor.getColumnIndex("Coord_Y")));
				list.add(map);
			}
			cursor.close();
			close();
			return list;
		}
		//lxr-----------获得公共设施数据数组------------------------------------------
		public  List<Map<String, Object>> getwcNUM(String floor,String typename) {
			List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
			String sql = "SELECT * FROM InfoTable Where Mapname='"+floor+"'AND Type='"+typename+"'";   Log.d("lxr",sql );
			if(db == null || !db.isOpen()){open();}
			Cursor cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("CompanyName", cursor.getString(cursor.getColumnIndex("CompanyName")));
				map.put("Coord_X", cursor.getFloat(cursor.getColumnIndex("Coord_X")));
				map.put("Coord_Y", cursor.getFloat(cursor.getColumnIndex("Coord_Y")));
				Log.d("lxr",map.get("CompanyName") +"  ,  "+map.get("Coord_X")+"  ,  "+map.get("Coord_Y"));
				list.add(map);
				close();
			}
			cursor.close();
			return list;
		}
		
		//lxr-----------获得公共设施列表数组------------------------------------------
		public  List<Map<String, Object>> getwclist(String dbname,String floor) {
			setDBpath(dbname);
			List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
			String sql = "select Type , count(Type) as num from InfoTable  Where MapName='"+floor+"' group by Type";   Log.d("lxr",sql );
			if(db == null || !db.isOpen()){open();}
			Cursor cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
			  if(!cursor.getString(cursor.getColumnIndex("Type")).equals("商铺")){
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("Type", cursor.getString(cursor.getColumnIndex("Type")));
				map.put("num", cursor.getInt(cursor.getColumnIndex("num")));
				//Log.d("lxr",map.get("Type") +"  ,  "+map.get("num"));
				list.add(map);}
			}
			close();
			cursor.close();
			return list;
		}
			
		
		//---------------获得查询轮径用的mallid-----------------------------------
		public String getMAllid(String dbname) {
			setDBpath(dbname);
			String Mallid = null ;
			String sql = "SELECT poiID FROM Mall";   Log.d("lxr",sql );
			if(db == null || !db.isOpen()){open();}
			Cursor cursor = db.rawQuery(sql, null);			
			while (cursor.moveToNext()) {
				Mallid = cursor.getString(cursor.getColumnIndex("poiID"));
			}
			Log.d("lxr",Mallid);
			close();
			cursor.close();
			return Mallid;
		}
		
		
		//-----------根据spotid获得商铺位置信息，用于跨地图路径------------------------------------------
		public PointF getSpotCoord(String spotId) {
			PointF s=new PointF();
				String sql = DrawConstants.QUERY_SPOT_INFO +"'"+spotId+"'" ;
				if(db == null || !db.isOpen())
				{
					open();	
				}
				Cursor cursor = db.rawQuery(sql, null);
				if (cursor.moveToNext()) {
					s.x= Float.parseFloat(cursor.getString(cursor.getColumnIndex("Coord_X")));
					s.y= Float.parseFloat(cursor.getString(cursor.getColumnIndex("Coord_Y")));
				}
				cursor.close();
				close();
				return s;
		}
		//-----------------获得建筑出口信息-----------------------
		public PointF getExit(String spotid){
			PointF s=new PointF();
			setDBpath(spotid);
			List<Map<String, Object>> wc=getwcNUM("Floor1","出入口");
			s.x= Float.parseFloat(wc.get(0).get("Coord_X").toString());
			s.y= Float.parseFloat(wc.get(0).get("Coord_Y").toString());
			close();
			return s;
		}
		//-----------获得商铺信息------------------------------------------
		public String getunityid(String spotId) {
				String sql = DrawConstants.QUERY_SPOT_INFO +"'"+spotId+"'" ;
				String unityid="";
				if(db == null || !db.isOpen())
				{
					open();	
				}
				Cursor  cursor = db.rawQuery(sql, null);
				if (cursor.moveToNext()) {
					unityid = cursor.getString(cursor.getColumnIndex("UnitID"));
				}
				Log.d("lxr",unityid+"!!!!!!!");
				cursor.close();
				this.close();
				return unityid ;
				
		}
		
}

